import pymysql

host = "localhost"
port = 3306
user = "root"
password = "root"
database = "finance"


# 专门处理增，删，改
def update(sql, param):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


# 专门处理查询
def select(sql, param):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    cursor.execute(sql, param)
    data = cursor.fetchall()
    con.commit()
    cursor.close()
    con.close()
    return data


def delete_user(name):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "delete from user where username = %s"
    param = [name]
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


def add_user(username, pwd, paypwd):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "insert into user(username,realname, password, paypwd, reputation) values (%s,'测试', MD5(%s), %s, '良好')"
    param = [username, pwd, paypwd]
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


def delete_userCard(username, cardNum):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "delete from bankcard where cardNum=%s and userId=(select id from user where username=%s)"
    param = [cardNum, username]
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


def add_userCard_full(cardBank, type, cardNum, username, balance, defaultl):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "insert into bankcard(cardBank,type,cardNum, userId, balance, defaultl) values (%s, %s, %s, (select id from user where username=%s),%s,%s)"
    param = [cardBank, type, cardNum, username, balance, defaultl]
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


def add_userCard(cardBank, type, cardNum, username):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "insert into bankcard(cardBank,type,cardNum, userId, balance,defaultl) values (%s, %s, %s, (select id from user where username=%s), 10000,0)"
    param = [cardBank, type, cardNum, username]
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


def delete_userLoan(username):
    c
    sql = "delete from loan where userId=(select id from user where username=%s)"
    param = [username]
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()
